![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
EXPLAIN PLAN InitializationWhen you run SQL statements with the EXPLAIN PLAN command, the output of EXPLAIN PLAN is put into a table with the default name plan_table. You must create this table before you can run EXPLAIN PLAN. The table can be created in one of two ways:
The plan_table table is define as follows: SQL> describe plan_table Name Null? Type ------------------------------- -------- ---- STATEMENT_ID VARCHAR2(30) TIMESTAMP DATE REMARKS VARCHAR2(80) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER(38) ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) OTHER LONG You do not have to name the table plan_table. You can direct EXPLAIN PLAN to use a table of another name if you want. Invoking EXPLAIN PLANInvoke the EXPLAIN PLAN command with the following Oracle command sequence: EXPLAIN PLAN SET STATEMENT_ID = 'Testing EXPLAIN PLAN' INTO plan_table FOR SQL Statement; STATEMENT_ID should reflect the statements function so that you can recognize it at a later time. The plan_table parameter is the name of the table you created as described in the preceding section. If the INTO clause is omitted, the command defaults to the name plan_table. Here is an example of a completed command: SQL> EXPLAIN PLAN 2 SET STATEMENT_ID = 'Testing EXPLAIN PLAN' 3 INTO plan_table 4 FOR 5 SELECT 6 SUBSTR(dogname,1,20) "Dog Name", 7 SUBSTR(breed_name,1,20) "Breed", 8 SUBSTR(owner,1,20) "Owner" 9 FROM 10 dogs, breeds 11 WHERE 12 dogs.breed = breeds.breed 13 ORDER BY 14 dogs.breed; Explained. The results of the EXPLAIN PLAN are written into the table plan_table. The following section explains how to retrieve the information in that table. Extracting EXPLAIN PLAN ResultsThe output of EXPLAIN PLAN is written to the table specified in the EXPLAIN PLAN command (by default, to the table named plan_table). You must extract this information in order to look at the results of EXPLAIN PLAN . The results can be displayed with a query such as this: SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||operation,1,30) ||' '||SUBSTR(options,1,15) ||' '||SUBSTR(object_name,1,15) ||' '||SUBSTR(DECODE(id, 0, 'Cost = '||position),1,12) "Statement Execution Plan", SUBSTR(optimizer, 1, 10) "Optimizer" FROM plan_table START WITH id = 0 AND statement_id = 'Testing EXPLAIN PLAN' CONNECT BY PRIOR id = parent_id AND statement_id = 'Testing EXPLAIN PLAN'; This query results in the following output: Statement Execution Plan Optimizer ------------------------------------------- --------- SELECT STATEMENT Cost = CHOOSE MERGE JOIN SORT JOIN TABLE ACCESS FULL BREEDS SORT JOIN TABLE ACCESS FULL DOGS 6 rows selected. If the optimizer had chosen a cost-based approach, the cost of the query would have been reflected in the first line of the optimization plan. Any features such as parallel query are also reflected here. With this information, you can tell whether your SQL statements take advantage of indexes, clusters, or hash clusters. If you use EXPLAIN PLAN, you can see precisely how your SQL statement is being executed and what effect any changes you make to the SQL statements have on the execution plan. If you change your SQL statements to take advantage of an index or a cluster, for example, you can see an immediate improvement. EXPLAIN PLAN output is ideal for pointing out your execution plan and may indicate that where you thought you were taking advantage of an index, you actually were not.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |